﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Text;

using Model;
namespace DAL
{
    #region class SQLDALStudentInfo
    /// <summary>
    /// it is suitable to the table with only one primary key,and can not deal with the aotu-increment key.
    /// Note:the first field in the table must be primary and autoincrement and int ,if exists 
    /// class:DAL StudentInfo 
    /// Author:wxc
    /// DateTime:2021/5/26 21:43:37
    /// </summary>	
    public class DALStudentInfo
    {
        #region SQL CONST		
        private const string INSERT_SQL = "INSERT INTO [StudentInfo] ([ID],[Name],[Sex],[Nation],[Phone],[Address],[email],[Collage],[Subject],[stats],[BMtime]) Values (@ID,@Name,@Sex,@Nation,@Phone,@Address,@email,@Collage,@Subject,@stats,@BMtime)";
        private const string UPDATE_SQL = "UPDATE [StudentInfo] SET Name=@Name,Sex=@Sex,Nation=@Nation,Phone=@Phone,Address=@Address,email=@email,Collage=@Collage,Subject=@Subject,stats=@stats,BMtime=@BMtime WHERE [ID]=@ID";
        private const string DEL_SQL = "DELETE FROM [StudentInfo] WHERE [ID]=@ID";
        private const string SELECT_ALL_SQL = "SELECT [ID],[Name],[Sex],[Nation],[Phone],[Address],[email],[Collage],[Subject],[stats],[BMtime] FROM [StudentInfo] order by iD desc";
        private const string SELECT_ONE_SQL = "SELECT [ID],[Name],[Sex],[Nation],[Phone],[Address],[email],[Collage],[Subject],[stats],[BMtime] FROM [StudentInfo] WHERE [ID]=@ID";
        private const string SELECT_WHERE_SQL = "SELECT [ID],[Name],[Sex],[Nation],[Phone],[Address],[email],[Collage],[Subject],[stats],[BMtime] FROM [StudentInfo] {$where} order by  iD desc";
        #endregion
        #region Paras const
        private const string PARA_ID = "@ID";
        private const string PARA_Name = "@Name";
        private const string PARA_Sex = "@Sex";
        private const string PARA_Nation = "@Nation";
        private const string PARA_Phone = "@Phone";
        private const string PARA_Address = "@Address";
        private const string PARA_email = "@email";
        private const string PARA_Collage = "@Collage";
        private const string PARA_Subject = "@Subject";
        private const string PARA_stats = "@stats";
        private const string PARA_BMtime = "@BMtime";
        #endregion
        #region Add Method
        /// <summary>
        /// Insert an item
        /// </summary>
        /// <param name="StudentInfo"></param>
        /// <returns>count of rows affected</returns>
        public int AddStudentInfo(StudentInfoEntity StudentInfo)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] StudentInfoParms = GetStudentInfoParametersInsert();
            //set values to paras
            StudentInfoParms[0].Value = StudentInfo.ID;
            StudentInfoParms[1].Value = StudentInfo.Name;
            StudentInfoParms[2].Value = StudentInfo.Sex;
            StudentInfoParms[3].Value = StudentInfo.Nation;
            StudentInfoParms[4].Value = StudentInfo.Phone;
            StudentInfoParms[5].Value = StudentInfo.Address;
            StudentInfoParms[6].Value = StudentInfo.email;
            StudentInfoParms[7].Value = StudentInfo.Collage;
            StudentInfoParms[8].Value = StudentInfo.Subject;
            StudentInfoParms[9].Value = StudentInfo.stats;
            StudentInfoParms[10].Value = StudentInfo.BMtime;
            //insert			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERT_SQL, StudentInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Mod Method
        /// <summary>
        /// Update an item
        /// </summary>
        /// <param name="StudentInfo"></param>
        /// <returns>count of rows affected</returns>
        public int ModStudentInfo(StudentInfoEntity StudentInfo)
        {
            int backvalue = 0;
            //get paras
            SqlParameter[] StudentInfoParms = GetStudentInfoParameters();
            //set values to paras
            StudentInfoParms[0].Value = StudentInfo.ID;
            StudentInfoParms[1].Value = StudentInfo.Name;
            StudentInfoParms[2].Value = StudentInfo.Sex;
            StudentInfoParms[3].Value = StudentInfo.Nation;
            StudentInfoParms[4].Value = StudentInfo.Phone;
            StudentInfoParms[5].Value = StudentInfo.Address;
            StudentInfoParms[6].Value = StudentInfo.email;
            StudentInfoParms[7].Value = StudentInfo.Collage;
            StudentInfoParms[8].Value = StudentInfo.Subject;
            StudentInfoParms[9].Value = StudentInfo.stats;
            StudentInfoParms[10].Value = StudentInfo.BMtime;
            //update			
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATE_SQL, StudentInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Del Method
        /// <summary>
        /// Del an item by primary key
        /// </summary>
        /// <param name="iD"></param> 
        /// <returns>count of rows affected</returns>
        public int DelStudentInfo(string iD)
        {
            int backvalue = 0;
            //primarykeys
            //get paras
            SqlParameter[] StudentInfoParms = GetStudentInfoPrimaryKeyParameters();
            //set values to paras
            StudentInfoParms[0].Value = iD;
            //delete
            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString))
            {
                backvalue = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, DEL_SQL, StudentInfoParms);
            }
            return backvalue;
        }
        #endregion
        #region Select Method
        /// <summary>
        /// Select an item by Primarykey
        /// </summary>
        /// <param name="iD"></param>
        /// <returns>an item</returns>
        public StudentInfoEntity GetStudentInfo(string iD)
        {
            StudentInfoEntity StudentInfo = new StudentInfoEntity();
            //primarykeys
            //get paras
            SqlParameter[] StudentInfoParms = GetStudentInfoPrimaryKeyParameters();
            //set values to paras
            StudentInfoParms[0].Value = iD;
            //select
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ONE_SQL, StudentInfoParms))
            {
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0)) StudentInfo.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) StudentInfo.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) StudentInfo.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) StudentInfo.Nation = reader.GetString(3);
                    if (!reader.IsDBNull(4)) StudentInfo.Phone = reader.GetString(4);
                    if (!reader.IsDBNull(5)) StudentInfo.Address = reader.GetString(5);
                    if (!reader.IsDBNull(6)) StudentInfo.email = reader.GetString(6);
                    if (!reader.IsDBNull(7)) StudentInfo.Collage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) StudentInfo.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) StudentInfo.stats = reader.GetInt32(9);
                    if (!reader.IsDBNull(10)) StudentInfo.BMtime = reader.GetDateTime(10);
                }
                else
                {
                    StudentInfo = null;
                }
            }
            return StudentInfo;

        }
        #endregion
        #region Select_All Method
        /// <summary>
        /// Select all items
        /// </summary>
        /// <returns>items</returns>
        public IList<StudentInfoEntity> GetStudentInfos()
        {
            IList<StudentInfoEntity> StudentInfos = new List<StudentInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                while (reader.Read())
                {
                    StudentInfoEntity StudentInfo = new StudentInfoEntity();
                    if (!reader.IsDBNull(0)) StudentInfo.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) StudentInfo.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) StudentInfo.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) StudentInfo.Nation = reader.GetString(3);
                    if (!reader.IsDBNull(4)) StudentInfo.Phone = reader.GetString(4);
                    if (!reader.IsDBNull(5)) StudentInfo.Address = reader.GetString(5);
                    if (!reader.IsDBNull(6)) StudentInfo.email = reader.GetString(6);
                    if (!reader.IsDBNull(7)) StudentInfo.Collage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) StudentInfo.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) StudentInfo.stats = reader.GetInt32(9);
                    if (!reader.IsDBNull(10)) StudentInfo.BMtime = reader.GetDateTime(10);
                    StudentInfos.Add(StudentInfo);
                }
            }
            return StudentInfos;
        }
        #endregion
        #region private common select by conditions
        /// <summary>
        /// private common select by conditions
        /// </summary>
        /// <returns>IList<StudentInfoEntity></returns>
        private IList<StudentInfoEntity> GetStudentInfosbyQueryString(string SQLstr)
        {
            IList<StudentInfoEntity> StudentInfos = new List<StudentInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQLstr))
            {
                while (reader.Read())
                {
                    StudentInfoEntity StudentInfo = new StudentInfoEntity();
                    if (!reader.IsDBNull(0)) StudentInfo.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) StudentInfo.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) StudentInfo.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) StudentInfo.Nation = reader.GetString(3);
                    if (!reader.IsDBNull(4)) StudentInfo.Phone = reader.GetString(4);
                    if (!reader.IsDBNull(5)) StudentInfo.Address = reader.GetString(5);
                    if (!reader.IsDBNull(6)) StudentInfo.email = reader.GetString(6);
                    if (!reader.IsDBNull(7)) StudentInfo.Collage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) StudentInfo.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) StudentInfo.stats = reader.GetInt32(9);
                    if (!reader.IsDBNull(10)) StudentInfo.BMtime = reader.GetDateTime(10);
                    StudentInfos.Add(StudentInfo);
                }
            }
            return StudentInfos;
        }
        #endregion
        #region public common select by conditions ,we can use it by changing the GetStudentInfosbyCondition(datatype condition) and sql
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<StudentInfoEntity></returns>
        public IList<StudentInfoEntity> GetStudentInfosbyCondition(string sql)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SELECT_WHERE_SQL);
            sb.Replace("{$where}", "where " + sql);
            return GetStudentInfosbyQueryString(sb.ToString());
        }
        #endregion
        #region Select Method Top n
        /// <summary>
        /// Select items by Columns
        /// </summary>
        /// <returns>IList<StudentInfoEntity></returns>
        public IList<StudentInfoEntity> GetStudentInfosTopN(int n)
        {
            IList<StudentInfoEntity> StudentInfos = new List<StudentInfoEntity>();
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECT_ALL_SQL, null))
            {
                int i = 0;
                while (reader.Read() && i < n)
                {
                    StudentInfoEntity StudentInfo = new StudentInfoEntity();
                    if (!reader.IsDBNull(0)) StudentInfo.ID = reader.GetString(0);
                    if (!reader.IsDBNull(1)) StudentInfo.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) StudentInfo.Sex = reader.GetString(2);
                    if (!reader.IsDBNull(3)) StudentInfo.Nation = reader.GetString(3);
                    if (!reader.IsDBNull(4)) StudentInfo.Phone = reader.GetString(4);
                    if (!reader.IsDBNull(5)) StudentInfo.Address = reader.GetString(5);
                    if (!reader.IsDBNull(6)) StudentInfo.email = reader.GetString(6);
                    if (!reader.IsDBNull(7)) StudentInfo.Collage = reader.GetString(7);
                    if (!reader.IsDBNull(8)) StudentInfo.Subject = reader.GetString(8);
                    if (!reader.IsDBNull(9)) StudentInfo.stats = reader.GetInt32(9);
                    if (!reader.IsDBNull(10)) StudentInfo.BMtime = reader.GetDateTime(10);
                    StudentInfos.Add(StudentInfo);
                    i++;
                }
            }
            return StudentInfos;
        }
        #endregion
        #region paras configuration
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to insert ,especially autoincrement
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetStudentInfoParametersInsert()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_StudentInfo_Insert");

            if (parms == null)
            {
                parms = new SqlParameter[] {
            new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Name,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Sex,SqlDbType.VarChar) ,
            new SqlParameter(PARA_Nation,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Phone,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Address,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_email,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Collage,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_Subject,SqlDbType.NVarChar) ,
            new SqlParameter(PARA_stats,SqlDbType.Int) ,
            new SqlParameter(PARA_BMtime,SqlDbType.DateTime)            };
                SQLHelper.CacheParameters("SQL_ALL_StudentInfo_Insert", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:all paras --- suitable to update
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetStudentInfoParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_ALL_StudentInfo_Update");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Name,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Sex,SqlDbType.VarChar) ,
                    new SqlParameter(PARA_Nation,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Phone,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Address,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_email,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Collage,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_Subject,SqlDbType.NVarChar) ,
                    new SqlParameter(PARA_stats,SqlDbType.Int) ,
                    new SqlParameter(PARA_BMtime,SqlDbType.DateTime)                    };
                SQLHelper.CacheParameters("SQL_ALL_StudentInfo_Update", parms);
            }
            return parms;
        }
        /// <summary>
        /// Internal function to get cached parameters:primary key paras
        /// </summary>
        /// <returns></returns>
        private static SqlParameter[] GetStudentInfoPrimaryKeyParameters()
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters("SQL_PrimaryKey_StudentInfo");

            if (parms == null)
            {
                parms = new SqlParameter[] {
                    new SqlParameter(PARA_ID,SqlDbType.NVarChar) ,
                    };
                SQLHelper.CacheParameters("SQL_PrimaryKey_StudentInfo", parms);
            }
            return parms;
        }
        #endregion
    }
    #endregion
}
